{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 170,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import matplotlib.pyplot as plt\n",
    "def save_fig(figname):\n",
    "    img_path = './img/' + figname  + '.svg'\n",
    "    plt.savefig(img_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "metadata": {},
   "outputs": [],
   "source": [
    "file_list = os.listdir('.')\n",
    "data = pd.read_excel(file_list[-3],sheet_name=2,engine='openpyxl',index_col=0) #打开附件一\n",
    "data.fillna(axis=0,method='ffill',inplace=True)\n",
    "iaqi_table = pd.read_excel('iaqi.xls')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "metadata": {},
   "outputs": [],
   "source": [
    "os.makedirs('./img',exist_ok=True)\n",
    "plt.rcParams['font.family'] = ['sans-serif']\n",
    "plt.rcParams['font.sans-serif'] = ['SimHei']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_from_25_to_28=data['2020-08-25':'2020-08-28']\n",
    "data_from_25_to_28[data_from_25_to_28.iloc[:,-2]>=800].iloc[:,-2]=0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>地点</th>\n",
       "      <th>SO2监测浓度(μg/m³)</th>\n",
       "      <th>NO2监测浓度(μg/m³)</th>\n",
       "      <th>PM10监测浓度(μg/m³)</th>\n",
       "      <th>PM2.5监测浓度(μg/m³)</th>\n",
       "      <th>O3最大八小时滑动平均监测浓度(μg/m³)</th>\n",
       "      <th>CO监测浓度(mg/m³)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>监测日期</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2020-08-25</th>\n",
       "      <td>监测点A</td>\n",
       "      <td>8.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>112.0</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-08-26</th>\n",
       "      <td>监测点A</td>\n",
       "      <td>7.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>92.0</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              地点  SO2监测浓度(μg/m³)  NO2监测浓度(μg/m³)  PM10监测浓度(μg/m³)  \\\n",
       "监测日期                                                                \n",
       "2020-08-25  监测点A             8.0            12.0             27.0   \n",
       "2020-08-26  监测点A             7.0            16.0             24.0   \n",
       "\n",
       "            PM2.5监测浓度(μg/m³)  O3最大八小时滑动平均监测浓度(μg/m³)  CO监测浓度(mg/m³)  \n",
       "监测日期                                                                 \n",
       "2020-08-25              11.0                   112.0            0.5  \n",
       "2020-08-26              10.0                    92.0            0.5  "
      ]
     },
     "execution_count": 174,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_from_25_to_28.head(2)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_numpy = data_from_25_to_28.iloc[:,-6:].to_numpy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4, 6)"
      ]
     },
     "execution_count": 176,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_numpy.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>空气质量分指数（IAQI）</th>\n",
       "      <th>0</th>\n",
       "      <th>50</th>\n",
       "      <th>100</th>\n",
       "      <th>150</th>\n",
       "      <th>200</th>\n",
       "      <th>300</th>\n",
       "      <th>400</th>\n",
       "      <th>500</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>一氧化碳（CO）24小时平均</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "      <td>24</td>\n",
       "      <td>36</td>\n",
       "      <td>48</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>二氧化硫（SO2）24小时平均</td>\n",
       "      <td>0</td>\n",
       "      <td>50</td>\n",
       "      <td>150</td>\n",
       "      <td>475</td>\n",
       "      <td>800</td>\n",
       "      <td>1600</td>\n",
       "      <td>2100</td>\n",
       "      <td>2620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>二氧化氮（NO2）24小时平均</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>80</td>\n",
       "      <td>180</td>\n",
       "      <td>280</td>\n",
       "      <td>565</td>\n",
       "      <td>750</td>\n",
       "      <td>940</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>臭氧（O3）最大8小时滑动平均</td>\n",
       "      <td>0</td>\n",
       "      <td>100</td>\n",
       "      <td>160</td>\n",
       "      <td>215</td>\n",
       "      <td>265</td>\n",
       "      <td>800</td>\n",
       "      <td>800</td>\n",
       "      <td>800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>粒径小于等于10颗粒物（PM10）24小时平均</td>\n",
       "      <td>0</td>\n",
       "      <td>50</td>\n",
       "      <td>150</td>\n",
       "      <td>250</td>\n",
       "      <td>350</td>\n",
       "      <td>420</td>\n",
       "      <td>500</td>\n",
       "      <td>600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>粒径小于等于2.5颗粒物（PM2.5）24小时平均</td>\n",
       "      <td>0</td>\n",
       "      <td>35</td>\n",
       "      <td>75</td>\n",
       "      <td>115</td>\n",
       "      <td>150</td>\n",
       "      <td>250</td>\n",
       "      <td>350</td>\n",
       "      <td>500</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               空气质量分指数（IAQI）  0   50  100  150  200   300   400   500\n",
       "0             一氧化碳（CO）24小时平均  0    2    4   14   24    36    48    60\n",
       "1            二氧化硫（SO2）24小时平均  0   50  150  475  800  1600  2100  2620\n",
       "2            二氧化氮（NO2）24小时平均  0   40   80  180  280   565   750   940\n",
       "3            臭氧（O3）最大8小时滑动平均  0  100  160  215  265   800   800   800\n",
       "4    粒径小于等于10颗粒物（PM10）24小时平均  0   50  150  250  350   420   500   600\n",
       "5  粒径小于等于2.5颗粒物（PM2.5）24小时平均  0   35   75  115  150   250   350   500"
      ]
     },
     "execution_count": 177,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "iaqi_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "metadata": {},
   "outputs": [],
   "source": [
    "iaqi_numpy = iaqi_table.iloc[:,-8:].to_numpy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[   0,    2,    4,   14,   24,   36,   48,   60],\n",
       "       [   0,   50,  150,  475,  800, 1600, 2100, 2620],\n",
       "       [   0,   40,   80,  180,  280,  565,  750,  940],\n",
       "       [   0,  100,  160,  215,  265,  800,  800,  800],\n",
       "       [   0,   50,  150,  250,  350,  420,  500,  600],\n",
       "       [   0,   35,   75,  115,  150,  250,  350,  500]], dtype=int64)"
      ]
     },
     "execution_count": 179,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "iaqi_numpy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "metadata": {},
   "outputs": [],
   "source": [
    "iaqi_numpy = iaqi_numpy[[1,2,-2,-1,-3,0],:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(6, 8)"
      ]
     },
     "execution_count": 181,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "iaqi_numpy.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "metadata": {},
   "outputs": [],
   "source": [
    "iaqi_numpy=iaqi_numpy.reshape(1,6,8)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_numpy=data_numpy.reshape(4,6,1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "metadata": {},
   "outputs": [],
   "source": [
    "index = data_numpy < iaqi_numpy\n",
    "index = index.argmax(-1)\n",
    "index_high = index\n",
    "index_low = index-1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 185,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[1, 1, 1, 1, 2, 1],\n",
       "       [1, 1, 1, 1, 1, 1],\n",
       "       [1, 1, 1, 1, 3, 1],\n",
       "       [1, 1, 1, 1, 3, 1]], dtype=int64)"
      ]
     },
     "execution_count": 185,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "index_high"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([  0.,  50., 100., 150., 200., 300., 400., 500.])"
      ]
     },
     "execution_count": 186,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "iaqi_score_map = iaqi_table.columns[-8:].to_numpy().astype(np.float64)\n",
    "iaqi_score_map"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((4, 6), (4, 6))"
      ]
     },
     "execution_count": 187,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "iaqi_hi,iaqi_lo=iaqi_score_map[index_high], iaqi_score_map[index_low]\n",
    "iaqi_numpy = iaqi_numpy.reshape(6,8)\n",
    "bp_hi, bp_lo = np.take(iaqi_numpy,index_high),np.take(iaqi_numpy, index_low) \n",
    "bp_hi.shape,iaqi_hi.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "metadata": {},
   "outputs": [],
   "source": [
    "iaqi_p = ((iaqi_hi - iaqi_lo)/(bp_hi - bp_lo) ) *( data_numpy.reshape(4,6)- bp_lo) + iaqi_lo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 189,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[  8,  12,  27,  11,  81,   0],\n",
       "       [  7,  16,  24,  10,  92,   0],\n",
       "       [  7,  31,  37,  23, 102,   0],\n",
       "       [  8,  30,  47,  33, 107,   0]])"
      ]
     },
     "execution_count": 189,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "iaqi_p = iaqi_p.astype(np.int32)\n",
    "\n",
    "iaqi_p"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 204,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 81,  92, 102, 107])"
      ]
     },
     "execution_count": 204,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "iaqi_p_index=iaqi_p.argmax(-1)\n",
    "iaqi_p_score = iaqi_p.max(-1)\n",
    "iaqi_p_score"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 195,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['O3最大八小时滑动平均监测浓度(μg/m³)', 'O3最大八小时滑动平均监测浓度(μg/m³)',\n",
       "       'O3最大八小时滑动平均监测浓度(μg/m³)', 'O3最大八小时滑动平均监测浓度(μg/m³)'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 195,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "primary_polute=data.columns[-6:][iaqi_p_index]\n",
    "primary_polute"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 199,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "优         0\n",
       "良        50\n",
       "轻度污染    100\n",
       "中度污染    150\n",
       "重度污染    200\n",
       "严重污染    300\n",
       "dtype: int64"
      ]
     },
     "execution_count": 199,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "iaqi_rank = pd.Series({'优':0 ,'良':50,'轻度污染':100,'中度污染':150,'重度污染':200,'严重污染':300})\n",
    "iaqi_rank"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 200,
   "metadata": {},
   "outputs": [],
   "source": [
    "iaqi_rank_numpy = iaqi_rank.to_numpy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 205,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[ True,  True, False, False, False, False],\n",
       "       [ True,  True, False, False, False, False],\n",
       "       [ True,  True,  True, False, False, False],\n",
       "       [ True,  True,  True, False, False, False]])"
      ]
     },
     "execution_count": 205,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "iaqi_p_score[:,np.newaxis] > iaqi_rank[np.newaxis,:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 209,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([2, 2, 3, 3], dtype=int64)"
      ]
     },
     "execution_count": 209,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "iaqi_rank_=(iaqi_p_score[:,np.newaxis] > iaqi_rank[np.newaxis,:]).argmin(-1)\n",
    "iaqi_rank_"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 214,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['轻度污染', '轻度污染', '中度污染', '中度污染'], dtype='object')"
      ]
     },
     "execution_count": 214,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "iaqi_rank_result=iaqi_rank[iaqi_rank_]\n",
    "iaqi_rank_result = iaqi_rank_result.index\n",
    "iaqi_rank_result #这是污染等级"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 215,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['O3最大八小时滑动平均监测浓度(μg/m³)', 'O3最大八小时滑动平均监测浓度(μg/m³)',\n",
       "       'O3最大八小时滑动平均监测浓度(μg/m³)', 'O3最大八小时滑动平均监测浓度(μg/m³)'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 215,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "primary_polute #这是主要污染物"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 216,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 81,  92, 102, 107])"
      ]
     },
     "execution_count": 216,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "iaqi_p_score #这是每天的iaqi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "4a845a6acb768a79faef9d7694aadcc28e614263549049aa1170665d0530acd2"
  },
  "kernelspec": {
   "display_name": "Python 3.6.13 64-bit (conda)",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.13"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
